package com.cn.bcyp.api.utils;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.cn.bcyp.api.config.ExcelDataConfig;
import com.cn.bcyp.api.domain.Http_Request_Data;
import jxl.read.biff.BiffException;
import jxl.write.WriteException;

public class ExcelUtil {

	/**
	 * 读取excel文件中数据
	 */
	public List<Http_Request_Data> readExcel(String filePath) {
		if (filePath == null || ExcelDataConfig.EMPTY.equals(filePath)) {
			return null;
		} else {
			String fileSuffix = getFileSuffix(filePath);
			if (!ExcelDataConfig.EMPTY.equals(fileSuffix)) {
				if (ExcelDataConfig.OFFICE_EXCEL_2003_FILESUFFIX.equals(fileSuffix)) {
					try {
						return readXls(filePath);
					} catch (Exception e) {
						throw new RuntimeException(e);
					}
				} else if (ExcelDataConfig.OFFICE_EXCEL_2010_FILESUFFIX.equals(fileSuffix)) {
					try {
						return readXlsx(filePath);
					} catch (IOException e) {
						throw new RuntimeException(e);
					}
				}
			} else {
				System.out.println(filePath + ExcelDataConfig.NOT_EXCEL_FILE);
			}
		}
		return null;
	}

	/**
	 * 读取xls扩展名数据
	 */
	public List<Http_Request_Data> readXls(String filePath) throws Exception {
		InputStream in = new FileInputStream(filePath);
		HSSFWorkbook hssfWorkbook = new HSSFWorkbook(in);
		Http_Request_Data http_Request_Data = null;
		List<Http_Request_Data> list = new ArrayList<Http_Request_Data>();

		for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
			HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
			if (hssfSheet == null) {
				continue;
			}
			for (int rowNum = 1; rowNum < hssfSheet.getLastRowNum(); rowNum++) {
				HSSFRow hssfRow = hssfSheet.getRow(rowNum);
				if (hssfRow != null) {
					http_Request_Data = new Http_Request_Data();
					HSSFCell ID = hssfRow.getCell(0);
					HSSFCell Test_is_exec = hssfRow.getCell(1);
					HSSFCell TestCase = hssfRow.getCell(2);
					HSSFCell Req_type = hssfRow.getCell(3);
					HSSFCell Interfac_classification = hssfRow.getCell(4);
					HSSFCell Req_host = hssfRow.getCell(5);
					HSSFCell Req_interface = hssfRow.getCell(6);
					HSSFCell Req_data = hssfRow.getCell(7);
					HSSFCell Req_phone = hssfRow.getCell(8);
					HSSFCell Req_sql = hssfRow.getCell(9);
					HSSFCell Result_expected = hssfRow.getCell(10);
					http_Request_Data.setID(getValue(ID));
					http_Request_Data.setTest_is_exec(getValue(Test_is_exec));
					http_Request_Data.setTestCase(getValue(TestCase));
					http_Request_Data.setReq_type(getValue(Req_type));
					http_Request_Data.setInterfac_classification(getValue(Interfac_classification));
					http_Request_Data.setReq_host(getValue(Req_host));
					http_Request_Data.setReq_interface(getValue(Req_interface));
					http_Request_Data.setReq_data(getValue(Req_data));
					http_Request_Data.setReq_phone(getValue(Req_phone));
					http_Request_Data.setReq_sql(getValue(Req_sql));
					http_Request_Data.setResult_expected(getValue(Result_expected));
					list.add(http_Request_Data);
				}
			}
		}
		return list;
	}

	/**
	 * 读取xlsx扩展名文件数据
	 * 
	 * @param filePath
	 * @return
	 * @throws IOException
	 */
	public List<Http_Request_Data> readXlsx(String filePath) throws IOException {
		InputStream in = new FileInputStream(filePath);
		XSSFWorkbook xssfWorkbook = new XSSFWorkbook(in);
		Http_Request_Data http_Request_Data = null;
		List<Http_Request_Data> list = new ArrayList<Http_Request_Data>();

		for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
			XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
			if (xssfSheet == null) {
				continue;
			}
			for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
				XSSFRow xssfRow = xssfSheet.getRow(rowNum);
				if (xssfRow != null) {
					http_Request_Data = new Http_Request_Data();
					XSSFCell ID = xssfRow.getCell(0);
					XSSFCell Test_is_exec = xssfRow.getCell(1);
					XSSFCell TestCase = xssfRow.getCell(2);
					XSSFCell Req_type = xssfRow.getCell(3);
					XSSFCell Interfac_classification = xssfRow.getCell(4);
					XSSFCell Req_host = xssfRow.getCell(5);
					XSSFCell Req_interface = xssfRow.getCell(6);
					XSSFCell Req_data = xssfRow.getCell(7);
					XSSFCell Req_phone = xssfRow.getCell(8);
					XSSFCell Req_sql = xssfRow.getCell(9);
					XSSFCell Result_expected = xssfRow.getCell(10);
					http_Request_Data.setID(getValue(ID));
					http_Request_Data.setTest_is_exec(getValue(Test_is_exec));
					http_Request_Data.setTestCase(getValue(TestCase));
					http_Request_Data.setReq_type(getValue(Req_type));
					http_Request_Data.setInterfac_classification(getValue(Interfac_classification));
					http_Request_Data.setReq_host(getValue(Req_host));
					http_Request_Data.setReq_sql(getValue(Req_sql));
					http_Request_Data.setReq_interface(getValue(Req_interface));
					http_Request_Data.setReq_data(getValue(Req_data));
					http_Request_Data.setReq_phone(getValue(Req_phone));
					http_Request_Data.setResult_expected(getValue(Result_expected));
					list.add(http_Request_Data);
				}
			}
		}
		return list;
	}

	private String getValue(XSSFCell xssfCell) {
		if (xssfCell.getCellType() == xssfCell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(xssfCell.getBooleanCellValue());
		} else if (xssfCell.getCellType() == xssfCell.CELL_TYPE_NUMERIC) {
			return String.valueOf(xssfCell.getNumericCellValue());
		} else {
			return String.valueOf(xssfCell.getStringCellValue());
		}
	}

	private String getValue(HSSFCell hssfCell) {
		if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(hssfCell.getBooleanCellValue());
		} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
			return String.valueOf(hssfCell.getNumericCellValue());
		} else {
			return String.valueOf(hssfCell.getStringCellValue());
		}
	}

	public static String getFileSuffix(String filePath) {
		if (filePath == null || ExcelDataConfig.EMPTY.equals(filePath.trim())) {
			return ExcelDataConfig.EMPTY;
		}
		if (filePath.contains(ExcelDataConfig.POINT)) {
			return filePath.substring(filePath.lastIndexOf(ExcelDataConfig.POINT) + 1, filePath.length());
		}
		return ExcelDataConfig.EMPTY;
	}

	public void updateExcel(String filePath, int column, int row, String Result_actual, int colorNum)
			throws BiffException, IOException {
		if (filePath == null || ExcelDataConfig.EMPTY.equals(filePath)) {
		} else {
			String fileSuffix = getFileSuffix(filePath);
			if (!ExcelDataConfig.EMPTY.equals(fileSuffix)) {
				if (ExcelDataConfig.OFFICE_EXCEL_2003_FILESUFFIX.equals(fileSuffix)) {
					try {
						writeExcelXls(filePath, column, row, Result_actual, colorNum);
					} catch (WriteException e) {
						throw new RuntimeException(e);
					}
				} else if (ExcelDataConfig.OFFICE_EXCEL_2010_FILESUFFIX.equals(fileSuffix)) {
					try {
						writeExcelXlsx(filePath, column, row, Result_actual, colorNum);
					} catch (WriteException e) {
						throw new RuntimeException(e);
					}
				}
			} else {
				System.out.println(filePath + ExcelDataConfig.NOT_EXCEL_FILE);
			}
		}
	}

	private void writeExcelXls(String filePath, int column, int row, String Result_actual, int colorNum)
			throws BiffException, IOException, WriteException {
		FileInputStream in = new FileInputStream(filePath);
		HSSFWorkbook wk = new HSSFWorkbook(in);
		in.close();
		HSSFSheet sheet = wk.getSheetAt(0);
		HSSFRow xfRow = sheet.getRow((short) row);
		HSSFCellStyle cellStyle = wk.createCellStyle();
		setXlsxColor(cellStyle, colorNum);
		if (xfRow == null) {
		} else {
			HSSFCell cell = xfRow.getCell(column);
			if (cell == null) {
				cell = xfRow.createCell(column);
				cell.setCellValue(Result_actual);
				cell.setCellStyle(cellStyle);
			} else {
				cell.setCellValue(Result_actual);
				cell.setCellStyle(cellStyle);
			}
		}
		FileOutputStream out = new FileOutputStream(filePath);
		wk.write(out);
		out.flush();
		out.close();
	}

	private void writeExcelXlsx(String filePath, int column, int row, String Result_actual, int colorNum)
			throws BiffException, IOException, WriteException {
		FileInputStream in = new FileInputStream(filePath);
		XSSFWorkbook wk = new XSSFWorkbook(in);
		in.close();
		XSSFSheet sheet = wk.getSheetAt(0);
		XSSFRow xfRow = sheet.getRow((short) row);
		// 将文本字面格式用到单元格上
		XSSFCellStyle cellStyle = wk.createCellStyle();
		setXlsxColor(cellStyle, colorNum);
		if (xfRow == null) {
		} else {
			XSSFCell cell = xfRow.getCell(column);
			if (cell == null) {
				cell = xfRow.createCell(column);
				cell.setCellValue(Result_actual);
				cell.setCellStyle(cellStyle);
			} else {
				cell.setCellValue(Result_actual);
				cell.setCellStyle(cellStyle);
			}
		}
		FileOutputStream out = new FileOutputStream(filePath);
		wk.write(out);
		out.flush();
		out.close();
	}


	private void setXlsxColor(XSSFCellStyle cellStyle, int num) {
		if (num == 1) {
			cellStyle.setFillPattern(XSSFCellStyle.BIG_SPOTS);
			cellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
			cellStyle.setFillBackgroundColor(IndexedColors.RED.getIndex());
		} else if (num == 2) {
			cellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
			cellStyle.setFillBackgroundColor(IndexedColors.RED.getIndex());
		} else if (num == 3) {
			cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
			cellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
		} else if (num == 4) {
			cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
			cellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
		} else if (num == 5) {
			cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
			cellStyle.setFillForegroundColor(IndexedColors.BLUE.getIndex());
		}
	}

	private void setXlsxColor(HSSFCellStyle cellStyle, int num) {
		if (num == 1) {
			cellStyle.setFillPattern(HSSFCellStyle.BIG_SPOTS);
			cellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
			cellStyle.setFillBackgroundColor(IndexedColors.RED.getIndex());
		} else if (num == 2) {
			cellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
			cellStyle.setFillBackgroundColor(IndexedColors.RED.getIndex());
		} else if (num == 3) {
			cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			cellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
		} else if (num == 4) {
			cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			cellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
		}
	}

}
